package cn.com.bod.fas.common.interceptor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Properties;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.google.gson.Gson;
import com.zaxxer.hikari.pool.HikariProxyPreparedStatement;

import cn.com.yusys.yusp.commons.util.StringUtil;

@Intercepts({ @Signature(type = ParameterHandler.class, method = "setParameters", args = { PreparedStatement.class }) })
public class OpBackupInterceptor extends AbstractInterceptor implements Interceptor {

	private static final Logger logger = LoggerFactory.getLogger(OpBackupInterceptor.class);

	private OpHistoryCacheService opHistoryCacheService;
	//历史表操作人
	private String creatoridValue;
	//历史表操作机构
	private String operatororgidValue;
	//历史表操作时间
	private String operatortimeValue;

	public OpBackupInterceptor(OpHistoryCacheService opHistoryCacheService) {
		this.opHistoryCacheService = opHistoryCacheService;
	}

	@Override
	public Object intercept(Invocation invocation) throws Throwable {
		Object object = null;
		HikariProxyPreparedStatement  ps = null;
		if( invocation.getArgs()[0] instanceof HikariProxyPreparedStatement ){
			ps = (HikariProxyPreparedStatement) invocation.getArgs()[0];
		}else{
			object = invocation.proceed();
			return object;
		}
		//通过mybatis对象获取当前执行的sql以及参数等各类信息
		DefaultParameterHandler statementHandler = (DefaultParameterHandler)invocation.getTarget();
		MappedStatement mappedStatement = this.getValue(statementHandler, "mappedStatement");
		SqlCommandType commandType = mappedStatement.getSqlCommandType();
		//如果当前sql是查询则不执行备份数据操作，直接跳过此拦截器
		if (commandType.equals(SqlCommandType.SELECT)) {
			return invocation.proceed();
		}

		BoundSql boundSql = this.getValue(statementHandler,"boundSql");
		Object parameterObject = statementHandler.getParameterObject();
		String executeSql = getExecuteSql(boundSql, mappedStatement.getConfiguration(), parameterObject);
		// 解析sql获得字段和值的映射

		String newValue = null;
		Connection conn = null;

		String tableName = "";
		String key = "";

		try {
			//从mybatis获取数据库连接
			conn = ps.getConnection();
			Map<String,String> oldValueMap = new HashMap<String,String>();
			switch (commandType) {
				case INSERT:
					tableName = searchTableFromInsertSql(executeSql);
					Gson gson = new Gson();
					newValue = gson.toJson(parameterObject);
					object = invocation.proceed();
					break;
				case UPDATE:
					Map<String, Object> sqlMap = new HashMap<>();
					String updatesql = getUpdateFields(executeSql, sqlMap);
					JSONObject json = new JSONObject(sqlMap);
					newValue = json.toString();
					tableName = searchTableFromUpdateSql(executeSql);
					key = getPrimaryKey(tableName,conn);
					oldValueMap = getOldValue(executeSql, conn, tableName,updatesql,commandType,key);
					object = invocation.proceed();
					break;
				case DELETE:
					tableName = searchTableFromDeleteSql(executeSql);
					key = getPrimaryKey(tableName,conn);
					oldValueMap = getOldValue(executeSql, conn, tableName, null, commandType,key);
					break;
				default:
					break;
			}

			//获取插入历史表的sql集合
			Map<String,String> historySqlMap = getHisSql(tableName, commandType, executeSql,conn,oldValueMap,key);
			recordHistory(historySqlMap, tableName, commandType,newValue,conn,oldValueMap);
			if (commandType.equals(SqlCommandType.DELETE)) {
				object = invocation.proceed();
			}
			return object;
		} catch (Exception e) {
			invocation.proceed();
			throw e;
		}
	}

	/**
	 * 根据不同sql语句返回最终备份记录的插入语句
	 * @param conn 连接
	 * @param tableName 表名
	 * @param commandType 操作类型
	 * @param executeSql 执行的完整sql，包括参数
	 * @param key 主键
	 * @return
	 * @throws SQLException
	 */
	private Map<String,String> getHisSql(String tableName, SqlCommandType commandType, String executeSql,Connection conn,Map<String, String> oldValueMap,String key) throws SQLException {
		StringBuilder builder = new StringBuilder();
		Map<String,String> historySqlMap = new HashMap<String,String>();
		builder.append("insert into ")
				.append("bm_data_op_log");
		builder.append("(OP_LOG_ID,TAB_NAME,OP_TYPE,OLD_VALUE, NEW_VALUE,URL,");
		if (SqlCommandType.INSERT.equals(commandType)) {
			//新增时候拼接sql语句
			builder.append("OPERATOR_ID,OPERATOR_ORG_ID,OPERATOR_TIME");
			builder.append(" )");
			builder.append(" values ( ?, ?, ?, ?, ? ,?");
			builder.append(" ,").append(creatoridValue);
			builder.append(" ,").append(operatororgidValue);
			builder.append(" ,").append("'").append(operatortimeValue).append("'");
			builder.append(")");
			historySqlMap.put("insert", builder.toString());
		} else {
			//遍历出所有的主键key
			Iterator<Entry<String, String>>  iter = oldValueMap.entrySet().iterator();
			List<String> primaryKeyList = new ArrayList<>();
			while (iter.hasNext()) {
				Map.Entry<String,String> entry = (Map.Entry<String,String>) iter.next();
				Object primaryKey = entry.getKey();
				primaryKeyList.add((String) primaryKey);
			}

			builder.append("OPERATOR_ID,OPERATOR_ORG_ID,OPERATOR_TIME");
			builder.append(" )");
			builder.append(" select ?, ?, ?, ?, ?, ?");
			builder.append(",RECENT_REVISER_ID,RECENT_REVISE_ORG_ID,RECENT_REVISE_TIME");
			builder.append(" from ").append(tableName).append(" where ");
			String temp = builder.toString();
			//如果查询出多条记录，把每个记录的主键取出拼接成sql
			for(int i =0 ; i < primaryKeyList.size(); i++ ) {
				String historySql = temp + key + "  = " + primaryKeyList.get(i);
				historySqlMap.put(primaryKeyList.get(i), historySql);
			}

		}

		return historySqlMap;
	}

	/**
	 * 执行sql到备份表 对于更新和删除如果存在多条数据，会循环遍历每一条sql，然后执行插表
	 * @param
	 * @param tableName,sql,commandType,operationPost,operationPre,conn
	 */
	private void recordHistory(Map<String,String> historySqlMap,String tableName,SqlCommandType commandType,String newValue,Connection conn,Map<String, String> oldValueMap) {
		PreparedStatement statement = null;
		try {
			//遍历出所有sql,插入历史表中
			for(String key : historySqlMap.keySet()) {
				String sql = (String) historySqlMap.get(key);
				statement = conn.prepareStatement(sql);
				statement.setString(1, UUID.randomUUID().toString().substring(0, 31));
				statement.setString(2, tableName);
				if(commandType.equals(SqlCommandType.INSERT)) {
					statement.setString(3, "I");
					statement.setString(4, null);
					statement.setString(5, newValue);
					statement.setString(6, null);
				}else if(commandType.equals(SqlCommandType.UPDATE)) {
					statement.setString(3, "U");
					statement.setString(4, oldValueMap.get(key));
					statement.setString(5, newValue);
					statement.setString(6, null);
				}else if(commandType.equals(SqlCommandType.DELETE)) {
					statement.setString(3, "D");
					statement.setString(4, oldValueMap.get(key));
					statement.setString(5, null);
					statement.setString(6, null);
				}
				statement.execute();
			}
		} catch (SQLException e) {
			logger.error("将数据插入历史表时执行sql失败！", e.getMessage());
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				logger.error("关闭statement失败！", e.getMessage());
			}
		}

	}

	/**
	 * 将参数和sql语句映射，返回最终执行的完整SQL语句
	 * @param boundSql
	 * @param configuration
	 * @param parameterObject
	 * @return
	 */
	private String getExecuteSql(BoundSql boundSql, Configuration configuration, Object parameterObject) {
		String executeSql = boundSql.getSql();
		TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
		//获取所有参数
		List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
		if (parameterMappings != null) {
			for (int i = 0; i < parameterMappings.size(); i++) {
				//获取某个参数
				ParameterMapping parameterMapping = parameterMappings.get(i);
				if (parameterMapping.getMode() != ParameterMode.OUT) {
					Object value;
					//参数名称
					String propertyName = parameterMapping.getProperty();

					if (boundSql.hasAdditionalParameter(propertyName)) {
						value = boundSql.getAdditionalParameter(propertyName);
					} else if (parameterObject == null) {
						value = null;
					} else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
						value = parameterObject;
					} else {
						MetaObject metaObject = configuration.newMetaObject(parameterObject);
						value = metaObject.getValue(propertyName);
					}
					// 对象.属性时 只存属性
					if (propertyName.contains(".")) {
						propertyName = propertyName.split("\\.")[1];
					}

					//将java中date数据类型转换为数据库中Timestamp
					String strValue = "";
					if(null!=value){
						if (Date.class.equals(value.getClass())) {
							strValue = new Timestamp(((Date) value).getTime()).toString();
						} else {
							strValue = value.toString();
						}
					}

					if("creatorId".equals(propertyName)) {
						creatoridValue = strValue;
					}
					if("createOrgId".equals(propertyName)) {
						operatororgidValue = strValue;
					}
					if("createTime".equals(propertyName)) {
						operatortimeValue = strValue;
					}

					executeSql = executeSql.replaceFirst("[?]", "'"+ strValue + "'");
				}
			}
		}
		return executeSql;
	}

	/**
	 * 获得当前表的全部数据库字段
	 * @param tableName
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	private List<String> getFields(String tableName,Connection conn) throws SQLException {
		if (opHistoryCacheService.getFields(tableName) == null) {
			OpHistoryCacheService.connThreadLocal.set(conn);
		}
		return opHistoryCacheService.getFields(tableName);
	}

	/**
	 * 获得当前表的主键
	 * @param tableName
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	private String getPrimaryKey(String tableName,Connection conn) throws SQLException {
		if (opHistoryCacheService.getPrimaryKey(tableName) == null) {
			OpHistoryCacheService.connThreadLocal.set(conn);
		}
		return opHistoryCacheService.getPrimaryKey(tableName);
	}

	/**
	 * 对于更新和删除操作,返回where关键字之后的操作条件
	 * @param sql
	 * @return
	 */
	private String getConditionFromSql(String sql) {
		String condition = "";
		sql = sql.replaceAll("[\\s\n]+", " ");
		Pattern p = Pattern.compile("(SET|FROM)(.*?)WHERE(.*)", Pattern.CASE_INSENSITIVE);
		Matcher matcher = p.matcher(sql);
		if (matcher.find()) {
			condition = matcher.group(3);
		}
		return condition;
	}

	/**
	 * 从insert语句中解析表名
	 * @param sql
	 * @return
	 */
	private String searchTableFromInsertSql(String sql) {
		String tableName = "";
		Pattern p = Pattern.compile("(INTO)(\\s)*(.*?)(\\s)*(\\(|SELECT|VALUE)", Pattern.CASE_INSENSITIVE);
		Matcher matcher = p.matcher(sql);
		if (matcher.find()) {
			tableName = matcher.group(3);
		}
		return tableName;
	}

	/**
	 * 从update语句中解析表名
	 * @param sql
	 * @return
	 */
	private String searchTableFromUpdateSql(String sql) {
		String tableName = "";
		Pattern p = Pattern.compile("(UPDATE)(\\s)*(.*?)(\\s)*(SET)", Pattern.CASE_INSENSITIVE);
		Matcher matcher = p.matcher(sql);
		if (matcher.find()) {
			tableName = matcher.group(3);
		}
		return tableName;
	}

	/**
	 * 从delete语句中解析表名
	 * @param sql
	 * @return
	 */
	private String searchTableFromDeleteSql(String sql) {
		String tableName = "";
		Pattern p = Pattern.compile("(FROM)(\\s)*(.*?)(\\s)*(WHERE)", Pattern.CASE_INSENSITIVE);
		Matcher matcher = p.matcher(sql);
		if (matcher.find()) {
			tableName = matcher.group(3);
		}
		return tableName;
	}

	/**
	 * 删除更新时，根据条件查询出所有数据,可能查询出多条记录,因此用map来存储,key为主键,value为旧数据
	 * @param executeSql
	 * @param conn
	 * @param tableName
	 * @param updatesql
	 * @param commandType
	 * @param key
	 * @return
	 * @throws SQLException
	 */
	private Map<String,String> getOldValue(String executeSql, Connection conn, String tableName,String updatesql,SqlCommandType commandType,String key) throws SQLException {
		String mysql = null;
		//获取删除时候where后条件
		String condition = getConditionFromSql(executeSql);
		Map<String,String> oldValue = new HashMap<String,String>();

		if (StringUtil.isBlank(condition) || StringUtil.isBlank(tableName)) {
			return null;
		}

		if(commandType.equals(SqlCommandType.UPDATE)) {
			mysql = " select "+ key + " ," +updatesql  + " from " + tableName + " where " + condition;
			oldValue = getupdateList(conn, mysql,key);
		} else if(commandType.equals(SqlCommandType.DELETE)) {
			mysql = "select * from " + tableName + " where " + condition;
			oldValue = getDBRecordList(conn, mysql,key);
		}
		return oldValue;
	}

	/**
	 * 根据条件查询出表中所有数据
	 * @param ms
	 * @param sql
	 * @return
	 */
	public Map<String,String> getDBRecordList(Connection conn, String sql,String key) {
		PreparedStatement statement = null;
		ResultSet rs = null;
		String deletePre = null;
		List<String> fields = new ArrayList<>();
		Map<String,String> recordList = new HashMap<>();
		try {
			statement = conn.prepareStatement(sql);
			rs = statement.executeQuery();
			// 获取所有字段
			ResultSetMetaData rsmd = rs.getMetaData();
			int count = rsmd.getColumnCount();

			for (int i = 0; i < count; i++) {
				fields.add(rsmd.getColumnName(i + 1));
			}

			while (rs.next()) {
				Map<String, Object> opRecordMap = new HashMap<>();
				for (String field : fields) {
					Object dbValue = rs.getObject(field);
					opRecordMap.put(field, dbValue);
				}

				JSONObject json = new JSONObject(opRecordMap);
				deletePre = json.toString();
				recordList.put(rs.getString(key), deletePre);
			}
			return recordList;
		} catch (SQLException e) {
			logger.error( e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (statement != null) {
					statement.close();
				}

			} catch (SQLException e) {
				logger.error("关闭rs,statement失败！", e.getMessage());
			}
		}
		return null;
	}

	/**
	 *
	 * @param conn
	 * @param sql
	 * @param key
	 * @return
	 */
	public Map<String,String> getupdateList(Connection conn, String sql,String key) {
		PreparedStatement statement = null;
		ResultSet rs = null;
		String deletePre = null;
		List<String> fields = new ArrayList<>();
		Map<String,String> updateList = new HashMap<>();
		try {
			statement = conn.prepareStatement(sql);
			rs = statement.executeQuery();
			// 获取所有字段
			ResultSetMetaData rsmd = rs.getMetaData();
			int count = rsmd.getColumnCount();

			for (int i = 0; i < count; i++) {
				fields.add(rsmd.getColumnName(i + 1));
			}

			while (rs.next()) {
				Map<String, Object> opRecordMap = new HashMap<>();
				for (String field : fields) {

					if(!field.equals(key)) {
						Object dbValue = rs.getObject(field);
						opRecordMap.put(field, dbValue);
					}

				}
				String id = rs.getString(key);
				JSONObject json = new JSONObject(opRecordMap);
				deletePre = json.toString();
				updateList.put(id, deletePre);
			}

			return updateList;
		} catch (SQLException e) {
			logger.error( e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (statement != null) {
					statement.close();
				}

			} catch (SQLException e) {
				logger.error("关闭rs,statement失败！", e.getMessage());
			}
		}
		return null;
	}

	/**
	 * 返回updateFields是需要更新的字段,将sql语句中的set字段=value中的字段和value做映射保存到sqlMap,sqlMap中存储了更新后的数据
	 * @param sql
	 * @param sqlMap
	 */
	private String getUpdateFields(String sql, Map<String, Object> sqlMap) {
		StringBuilder builder = new StringBuilder();
		String updateFields = "";
		String values = "";
		sql = sql.replaceAll("[\\s\n]+", " ");
		Pattern p = Pattern.compile("SET(.*)WHERE", Pattern.CASE_INSENSITIVE);
		Matcher matcher = p.matcher(sql);
		if (matcher.find()) {
			values = matcher.group(1);
		}
		if (sql.toLowerCase().startsWith("update")) {
			String[] columns = values.split(",");
			for (String column : columns) {
				if (column.contains("=")) {
					String[] kvs = column.split("=");
					sqlMap.put(kvs[0].trim(), kvs[1].trim());
					builder.append(kvs[0].trim() + ",");
				}
			}
		}
		updateFields = builder.toString();
		//截取,之前的字符串
		String updateSql = updateFields.substring(0, updateFields.lastIndexOf(","));
		return updateSql;
	}

	@Override
	public Object plugin(Object target) {
		return Plugin.wrap(target, this);
	}

	@Override
	public void setProperties(Properties properties) {

	}

}
